package com.dy.yunying.biz.dao.clickhouse3399.impl;

import com.dy.yunying.api.entity.AdKpi;
import com.dy.yunying.api.vo.AdKpiVo;
import com.dy.yunying.biz.utils.DateUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.util.List;
import java.util.Objects;

/**
 * @ClassName AdKpiDataDao
 * @Description todo
 * @Author nieml
 * @Time 2021/7/9 9:27
 * @Version 1.0
 **/

@Component
@Slf4j
public class AdKpiDataDao {

	@Resource(name="clickhouseTemplate")
	private JdbcTemplate clickhouseTemplate;

	public List<AdKpiVo> queryKpiData(AdKpi req) {
		StringBuilder sql = getSql(req);
		log.info("Kpi数据查询sql:[{}]", sql.toString());

		List<AdKpiVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<AdKpiVo>(AdKpiVo.class));
		return list;
	}


	//拼接完整Kpi指标计算的sql
	private StringBuilder getSql(AdKpi req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder usrnameNums = getUsrnameNums(req);
		StringBuilder newPay = getNewPay(req);
		StringBuilder allPay = getAllPay(req);
		StringBuilder ad = getAd(req);
		String selectCondition = getSelectCondition(req);
		String groupByCondition = getGroupByCondition(req);
		querySql.append(" select \n" +
				selectCondition +
				" sum(usrnamenums) realLevel,\n" +
				" sum(worth1) worth1,\n" +
				" sum(worth30) worth30,\n" +
				" sum(cost) cost,\n" +
				" sum(userfee+ givemoney) totalPayfee \n" +
				" from\n");
		//t1000
		querySql.append(" ( ");
		querySql.append(" SELECT\n" +
				selectCondition +
				"\t\tadid,\n" +
				"\t\tifnull(usrnamenums,0) usrnamenums,--新增设备注册设备数\n" +
				"\t\tcast( ifnull(worth1,cast(0 as decimal(18,3))) as UInt64) worth1, -- 新增注册设备当日充值金额\n" +
				"\t\tcast( ifnull(worth30,cast(0 as decimal(18,3))) as UInt64) worth30,--新增注册设备首日至30日充值金额\n" +
				"\t\tifnull(cost,cast(0 as decimal(18,3))) cost, --返点消耗\n" +
				"\t\tcast(ifnull(userfee,cast(0 as decimal(18,3))) as UInt64) userfee, -- 新增注册设备当日充值金额\n" +
				"\t\tcast(ifnull(givemoney,cast(0 as decimal(18,3))) as UInt64) givemoney " +
				"from ");
		//t100
		querySql.append(" ( ");
		querySql.append("  -- 新增注册设备数 + 新增付费\n" +
				"\t\t\tSELECT\n" +
				selectCondition +
				"\t\t\t\tadid,\n" +
				"\t\t\t\tusrnamenums,\n" +
				"\t\t\t\tworth1,\n" +
				"\t\t\t\tworth30\n" +
				"\t\t\tFROM ");
		querySql.append(usrnameNums);
		querySql.append(" FULL JOIN ");
		querySql.append(newPay);
		querySql.append(" USING (adid," + groupByCondition + " ) ");
		querySql.append(" ) t100 ");
		querySql.append(" FULL JOIN ");
		//t200
		querySql.append(" ( ");
		querySql.append(" -- 广告消耗+平台付费指标（付费金额）\n" +
				"\t\tSELECT\n" +
				selectCondition +
				"\t\t\tadid,\n" +
				"\t\t\tcost,\n" +
				"\t\t\trudecost,\n" +
				"\t\t\tuserfee,\n" +
				"\t\t\tgivemoney\n" +
				"\t\tFROM ");
		querySql.append(allPay);
		querySql.append(" FULL JOIN ");
		querySql.append(ad);
		querySql.append(" USING (adid," + groupByCondition + " ) ");
		querySql.append(" ) t200 ");
		querySql.append(" USING (adid," + groupByCondition + " ) ");
		querySql.append(" ) t1000 ");
		querySql.append(" group by ");
		querySql.append(groupByCondition);
		querySql.append( " limit 1 ");
		return querySql;
	}

	//group
	private String getGroupByCondition(AdKpi req){
		StringBuilder groupSql = new StringBuilder();
		Integer pgid = req.getPgid();
		Integer hierarchy = req.getHierarchy();
		String hierarchyDim = getHierarchyDim(hierarchy);
		groupSql.append(" period ");
		groupSql.append(" , " + hierarchyDim);
		if(pgid != 0){
			groupSql.append(" ,pgid ");
		}
		groupSql.append(" ,os ");
		return groupSql.toString();
	}


	//select
	//类别 多个用逗号分隔【系统 os,主游戏 pgid,部门 deptId,广告计划 adId,  投放人investor
	private String getSelectCondition(AdKpi req){
		StringBuilder selectSql = new StringBuilder();
		Integer pgid = req.getPgid();
		Integer hierarchy = req.getHierarchy();
		String hierarchyDim = getHierarchyDim(hierarchy);
		selectSql.append(" period ");
		selectSql.append(" , " + hierarchyDim);
		if(pgid != 0){
			selectSql.append(" ,pgid ");
		}
		selectSql.append(" ,os ");
		selectSql.append(",");
		return selectSql.toString();
	}

	private String getHierarchyDim(int hierarchy){
		switch (hierarchy){
			case 1:
				return "deptId";
//			case 2:
//				return "deptId";
			case 3:
				return "investor";
			default:
				return "deptId";
		}
	}

	//where 筛选条件
	public String getWhereCondition(AdKpi req, String bieming) {
		StringBuilder sqlCondition = new StringBuilder();
		//系统
		Integer os = req.getOs();
		//主游戏
		Integer pgid = req.getPgid();
		//部门+投放人
		Integer hierarchy = req.getHierarchy();
		String hierarchyDim = getHierarchyDim(hierarchy);
		Integer hierarchyId = req.getHierarchyId();
		//日期
		Integer yearMonth = req.getYearMonth();

		if (StringUtils.isBlank(bieming)) {
			if ("deptId".equals(hierarchyDim)) {
				sqlCondition.append(" AND deptId = ").append(hierarchyId).append(" ");
			}
			if ("investor".equals(hierarchyDim)) {
				sqlCondition.append(" AND investor = ").append(hierarchyId).append(" ");
			}
			if (pgid != 0) {
				sqlCondition.append(" and pgid  = " + pgid);
			}
			if (Objects.nonNull(os)) {
				sqlCondition.append(" and os  = ").append(os);
			}
			return sqlCondition.toString();
		} else {
			if ("deptId".equals(hierarchyDim)) {
				sqlCondition.append(" AND ").append(bieming).append(".deptId = ").append(hierarchyId).append(" ");
			}
			if ("investor".equals(hierarchyDim)) {
				sqlCondition.append(" AND ").append(bieming).append(".investor = ").append(hierarchyId).append(" ");
			}
			if (pgid != 0) {
				sqlCondition.append(" and ").append(bieming).append(".pgid  = ").append(pgid);
			}
			if (Objects.nonNull(os)) {
				sqlCondition.append(" and ").append(bieming).append(".os  = ").append(os);
			}
			return sqlCondition.toString();
		}
	}

	//计算新增注册设备数 t001
	private StringBuilder getUsrnameNums(AdKpi req) {
		StringBuilder querySql = new StringBuilder();
		String selectCondition = getSelectCondition(req);
		String groupByCondition = getGroupByCondition(req);
		String whereCondition = getWhereCondition(req, "");
		Integer yearMonth = req.getYearMonth();
		String yearMonthStr = yearMonth.toString();
		String firstDayOfMonth = DateUtils.getFirstDayOfMonth(yearMonthStr);
		String lastDayOfMonth = DateUtils.getLastDayOfMonth(yearMonthStr);
		querySql.append(" ( ");
		querySql.append(" \n" +
				"\t\t\t\t-- 新增注册设备数\n" +
				"\t\t\t\tSELECT\n" +
				selectCondition +
				"\t\t\t\t\tadid,\n" +
				"\t\t\t\t\tCOUNT(DISTINCT ud.dr_kid) usrnamenums\n" +
				"\t\t\t\tfrom\n" +
				"\t\t\t\t\t(\n" +
				"\t\t\t\t\tSELECT\n" +
				"\t\t\t\t\t\ta.day day,\n" +
				"\t\t\t\t\t\tparseDateTimeBestEffort(toString(day)) dayDate,\n" +
				"\t\t\t\t\t\ttoString(toYear(dayDate)) yearStr,\n" +
				"\t\t\t\t\t\ttoMonth(dayDate) monthInt,\n" +
				"\t\t\t\t\t\tconcat(yearStr,\n" +
				"\t\t\t\t\t\tIF(10>monthInt,\n" +
				"\t\t\t\t\t\t'0',\n" +
				"\t\t\t\t\t\t''),\n" +
				"\t\t\t\t\t\ttoString(monthInt)) AS monthStr,\n" +
				"\t\t\t\t\t\tmonthStr AS period,\n" +
				"\t\t\t\t\t\ta.pgid pgid,\n" +
				"\t\t\t\t\t\ta.gameid gameid,\n" +
				"\t\t\t\t\t\ta.os os,\n" +
				"\t\t\t\t\t\twpc.manage investor,\n" +
				"\t\t\t\t\t\tosu.dept_id deptId,\n" +
				"\t\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\t\twhen ad.adid is not null THEN ad.adid\n" +
				"\t\t\t\t\t\t\telse ''\n" +
				"\t\t\t\t\tend) as adid,\n" +
				"\t\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\t\twhen ad.adname is not null THEN ad.adname\n" +
				"\t\t\t\t\t\t\telse ''\n" +
				"\t\t\t\t\tend) as adidName,\n" +
				"\t\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\t\twhen ad.adaccount is not null THEN ad.adaccount\n" +
				"\t\t\t\t\t\t\telse ''\n" +
				"\t\t\t\t\tend) as adAccount,\n" +
				"\t\t\t\t\t\ta.uuid uuid,\n" +
				"\t\t\t\t\t\ta.kid kid,\n" +
				"\t\t\t\t\t\ta.receivetime receivetime,\n" +
				"\t\t\t\t\t\tad.advert_id advert_id\n" +
				"\t\t\t\t\tfrom\n" +
				"\t\t\t\t\t\tthirty_game_device_reg a\n" +
				"\t\t\t\t\tLEFT JOIN v_thirty_ad_device ad on\n" +
				"\t\t\t\t\t\ta.uuid = ad.uuid\n" +
				"\t\t\t\t\t\tand a.pgid = ad.pgid\n" +
				"\t\t\t\t\t\tand a.day = ad.`day`\n" +
				"\t\t\t\t\tLEFT JOIN odsmysql_wan_promotion_channel_v3 wpc ON\n" +
				"\t\t\t\t\t\twpc.isdelete = 0\n" +
				"\t\t\t\t\t\tAND wpc.parent_code = a.parentchl\n" +
				"\t\t\t\t\t\tAND wpc.chncode = a.chl\n" +
				"\t\t\t\t\tLEFT JOIN odsmysql_sys_user osu ON\n" +
				"\t\t\t\t\t\twpc.manage = osu.user_id\n" +
				"\t\t\t\t\tLEFT JOIN v_odsmysql_adid vad ON\n" +
				"\t\t\t\t\t\tad.adid = vad.adid\n" +
				"\t\t\t\t\t\tAND ad.ctype = vad.ctype\n" +
				"\t\t\t\t\twhere\n" +
				"\t\t\t\t\t\t1 = 1\n" +
				"\t\t\t\t\t\tand a.day >= \n" + firstDayOfMonth +
				"\t\t\t\t\t\tand a.day <= \n" + lastDayOfMonth +
				"\t\t\t\t\t\t ) reg\n" +
				"\t\t\t\tinner JOIN (\n" +
				"\t\t\t\t\tselect\n" +
				"\t\t\t\t\t\tur.kid ur_kid,\n" +
				"\t\t\t\t\t\targMax(dr.kid,\n" +
				"\t\t\t\t\t\tdr.receivetime) dr_kid,\n" +
				"\t\t\t\t\t\targMax(ur.usrname,\n" +
				"\t\t\t\t\t\tdr.receivetime) usrname\n" +
				"\t\t\t\t\tfrom\n" +
				"\t\t\t\t\t\tv_game_account_reg ur\n" +
				"\t\t\t\t\tINNER join odsmysql_wan_game g on\n" +
				"\t\t\t\t\t\tur.gameid = g.id\n" +
				"\t\t\t\t\tINNER join thirty_game_device_reg dr on\n" +
				"\t\t\t\t\t\tur.uuid = dr.uuid\n" +
				"\t\t\t\t\t\tand g.pgid = dr.pgid\n" +
				"\t\t\t\t\twhere\n" +
				"\t\t\t\t\t\t1 = 1\n" +
				"\t\t\t\t\t\tand ur.receivetimes >= dr.receivetime\n" +
				"\t\t\t\t\t\tand dr.day >= \n" + firstDayOfMonth +
				"\t\t\t\t\t\tand dr.day <= \n" + lastDayOfMonth +
				"\t\t\t\t\tgroup by\n" +
				"\t\t\t\t\t\tur.kid ) ud on\n" +
				"\t\t\t\t\treg.kid = ud.dr_kid\n" +
				"\t\t\t\twhere\n" +
				"\t\t\t\t\t1 = 1\n" +
				whereCondition +
				"\t\t\t\tGROUP BY\n" +
				"\t\t\t\t\tadid,\n" +
				groupByCondition +
				"\t\t\t\t ");
		querySql.append(" ) t001 ");
		return querySql;
	}

	//计算新增付费相关指标 t002
	private StringBuilder getNewPay(AdKpi req) {
		String selectCondition = getSelectCondition(req);
		String groupByCondition = getGroupByCondition(req);
		String whereCondition = getWhereCondition(req, "");
		Integer yearMonth = req.getYearMonth();
		String yearMonthStr = yearMonth.toString();
		String firstDayOfMonth = DateUtils.getFirstDayOfMonth(yearMonthStr);
		String lastDayOfMonth = DateUtils.getLastDayOfMonth(yearMonthStr);
		StringBuilder querySql = new StringBuilder();
		querySql.append(" ( ");
		querySql.append(" \n" +
				"\t\t\t\t-- 新增付费相关指标\n" +
				"\t\t\t\t-- paydeviceAll：新增充值设备数\n" +
				"\t\t\t\t-- worth1：首日新增充值金额\n" +
				"\t\t\t\t-- paydevice1：首日 新增充值设备数\n" +
				"\t\t\t\t-- worth7 ： 当周充值金额\n" +
				"\t\t\t\t-- paydevice7：\n" +
				"\t\t\t\t--userfeeAll：新增充值金额(现金)\n" +
				"\t\t\t\t-- givemoneyAll：新增充值金额（代金券）\n" +
				"\t\t\t\tselect\n" +
				selectCondition +
				"\t\t\t\t\tadid,\n" +
				"\t\t\t\t\tSUM(worth1) worth1,\n" +
				"\t\t\t\t\tSUM(worth30) worth30\n" +
				"\t\t\t\tfrom\n" +
				"\t\t\t\t\t(\n" +
				"\t\t\t\t\tselect\n" +
				"\t\t\t\t\t\targMax(reg.day,\n" +
				"\t\t\t\t\t\treg.receivetime) regDay,\n" +
				"\t\t\t\t\t\tregDay AS day,\n" +
				"\t\t\t\t\t\tparseDateTimeBestEffort(toString(day)) dayDate,\n" +
				"\t\t\t\t\t\ttoString(toYear(dayDate)) yearStr,\n" +
				"\t\t\t\t\t\ttoMonth(dayDate) monthInt,\n" +
				"\t\t\t\t\t\ttoYearWeek(dayDate,\n" +
				"\t\t\t\t\t\t3) weekInt,\n" +
				"\t\t\t\t\t\tconcat(yearStr,\n" +
				"\t\t\t\t\t\tIF(10>monthInt,\n" +
				"\t\t\t\t\t\t'0',\n" +
				"\t\t\t\t\t\t''),\n" +
				"\t\t\t\t\t\ttoString(monthInt)) AS monthStr,\n" +
				"\t\t\t\t\t\tconcat(toString(weekInt),\n" +
				"\t\t\t\t\t\t'周') AS weekStr,\n" +
				"\t\t\t\t\t\tmonthStr AS period,\n" +
				"\t\t\t\t\t\targMax(reg.pgid,\n" +
				"\t\t\t\t\t\treg.receivetime) pgid,\n" +
				"\t\t\t\t\t\targMax(reg.gameid,\n" +
				"\t\t\t\t\t\treg.receivetime) AS gameid,\n" +
				"\t\t\t\t\t\targMax(reg.os,\n" +
				"\t\t\t\t\t\treg.receivetime) AS os,\n" +
				"\t\t\t\t\t\targMax(wpc.manage,\n" +
				"\t\t\t\t\t\treg.receivetime) investor,\n" +
				"\t\t\t\t\t\targMax(osu.dept_id,\n" +
				"\t\t\t\t\t\treg.receivetime) deptId,\n" +
				"\t\t\t\t\t\targMax(ad.adid,\n" +
				"\t\t\t\t\t\treg.receivetime) adidTmp,\n" +
				"\t\t\t\t\t\targMax(ad.adname,\n" +
				"\t\t\t\t\t\treg.receivetime) adidName,\n" +
				"\t\t\t\t\t\targMax(ad.adaccount,\n" +
				"\t\t\t\t\t\treg.receivetime) adAccount,\n" +
				"\t\t\t\t\t\t(case when adidTmp is not null THEN adidTmp else '' end) as adid,\n" +
				"\t\t\t\t\t\trc.kid rckid,\n" +
				"\t\t\t\t\t\targMax(reg.kid,\n" +
				"\t\t\t\t\t\treg.receivetime) regkid,\n" +
				"\t\t\t\t\t\targMax(rc.day,reg.receivetime) payday,parseDateTimeBestEffort(toString(payday)) paydayDate,\n" +
				"\t\t\t\t\t\ttoString(toYear(paydayDate)) payYearStr,\n" +
				"\t\t\t\t\t\ttoMonth(paydayDate) payMonthInt,\n" +
				"\t\t\t\t\t\ttoYearWeek(paydayDate,3) payWeekInt,\n" +
				"\t\t\t\t\t\tconcat(payYearStr,IF(10>payMonthInt,'0',''),\n" +
				"\t\t\t\t\t\ttoString(payMonthInt)) AS payMonthStr,\n" +
				"\t\t\t\t\t\tdateDiff('day',parseDateTimeBestEffort(toString(day)),today() ) as cur_diff,\n" +
				"\t\t\t\t\t\tdateDiff('day',parseDateTimeBestEffort(toString(day)),parseDateTimeBestEffort(toString(payday))) as pay_diff,\n" +
				"\t\t\t\t\t\targMax(rc.uuid,reg.receivetime) AS payuuid,\n" +
				"\t\t\t\t\t\targMax(rc.fee,reg.receivetime) fee,\n" +
				"\t\t\t\t\t\targMax(rc.givemoney,reg.receivetime) givemoney,\n" +
				"\t\t\t\t\t\t(case when pay_diff = 0 then fee + givemoney else 0 end) worth1,\n" +
				"\t\t\t\t\t\t(case when monthStr = payMonthStr then fee + givemoney else 0 end) worth30,\n" +
				"\t\t\t\t\t\targMax(rc.createtime,reg.receivetime) createtime\n" +
				"\t\t\t\t\tfrom\n" +
				"\t\t\t\t\t\toriginal_user_recharge rc\n" +
				"\t\t\t\t\tleft join odsmysql_wan_game g on\n" +
				"\t\t\t\t\t\trc.gameid = g.id\n" +
				"\t\t\t\t\tLEFT join thirty_game_device_reg reg on\n" +
				"\t\t\t\t\t\trc.uuid = reg.uuid\n" +
				"\t\t\t\t\t\tand g.pgid = reg.pgid\n" +
				"\t\t\t\t\tLEFT JOIN v_thirty_ad_device ad on\n" +
				"\t\t\t\t\t\treg.uuid = ad.uuid\n" +
				"\t\t\t\t\t\tand reg.day = ad.day\n" +
				"\t\t\t\t\t\tand reg.pgid = ad.pgid\n" +
				"\t\t\t\t\t\t-- 拿到投放人\n" +
				"\t\t\t\t\tLEFT JOIN odsmysql_wan_promotion_channel_v3 wpc ON\n" +
				"\t\t\t\t\t\twpc.isdelete = 0\n" +
				"\t\t\t\t\t\tAND wpc.parent_code = reg.parentchl\n" +
				"\t\t\t\t\t\tAND wpc.chncode = reg.chl\n" +
				"\t\t\t\t\t\t-- 部门id\n" +
				"\t\t\t\t\tLEFT JOIN odsmysql_sys_user osu ON\n" +
				"\t\t\t\t\t\twpc.manage = osu.user_id\n" +
				"\t\t\t\t\t\t-- 广告账户\n" +
				"\t\t\t\t\tLEFT JOIN v_odsmysql_adid vad ON\n" +
				"\t\t\t\t\t\tad.adid = vad.adid\n" +
				"\t\t\t\t\t\tAND ad.ctype = vad.ctype\n" +
				"\t\t\t\t\twhere\n" +
				"\t\t\t\t\t\t1 = 1\n" +
				"\t\t\t\t\t\tand reg.`day` >= \n" + firstDayOfMonth +
				"\t\t\t\t\t\tand reg.`day` <= \n" + lastDayOfMonth +
				"\t\t\t\t\t\tAND rc.`day` >= reg.`day`\n" +
				"\t\t\t\t\tgroup by\n" +
				"\t\t\t\t\t\trc.kid ) a\n" +
				"\t\t\t\twhere\n" +
				"\t\t\t\t\t1 = 1\n" +
				whereCondition +
				"\t\t\t\tGROUP BY\n" +
				"\t\t\t\t\tadid,\n" +
				groupByCondition
				);
		querySql.append(" ) t002 ");
		return querySql;
	}

	//计算累计付费相关指标 t003
	private StringBuilder getAllPay(AdKpi req) {
		String selectCondition = getSelectCondition(req);
		String groupByCondition = getGroupByCondition(req);
		String whereCondition = getWhereCondition(req, "");
		Integer yearMonth = req.getYearMonth();
		String yearMonthStr = yearMonth.toString();
		String firstDayOfMonth = DateUtils.getFirstDayOfMonth(yearMonthStr);
		String lastDayOfMonth = DateUtils.getLastDayOfMonth(yearMonthStr);
		StringBuilder querySql = new StringBuilder();
		querySql.append(" ( ");
		querySql.append(" \n" +
				"\t\t\t-- 累计付费\n" +
				"\t\t\tselect\n" +
				selectCondition +
				"\t\t\t\tadid,\n" +
				"\t\t\t\tSUM(fee) userfee,\n" +
				"\t\t\t\tSUM(givemoney) givemoney\n" +
				"\t\t\tfrom\n" +
				"\t\t\t\t(\n" +
				"\t\t\t\tselect\n" +
				"\t\t\t\t\targMax(reg.day,\n" +
				"\t\t\t\t\treg.receivetime) regDay,\n" +
				"\t\t\t\t\targMax(rc.day,\n" +
				"\t\t\t\t\treg.receivetime) payday,\n" +
				"\t\t\t\t\tpayday AS day,\n" +
				"\t\t\t\t\tparseDateTimeBestEffort(toString(day)) dayDate,\n" +
				"\t\t\t\t\ttoString(toYear(dayDate)) yearStr,\n" +
				"\t\t\t\t\ttoMonth(dayDate) monthInt,\n" +
				"\t\t\t\t\tconcat(yearStr,\n" +
				"\t\t\t\t\tIF(10>monthInt,\n" +
				"\t\t\t\t\t'0',\n" +
				"\t\t\t\t\t''),\n" +
				"\t\t\t\t\ttoString(monthInt)) AS monthStr,\n" +
				"\t\t\t\t\tmonthStr AS period,\n" +
				"\t\t\t\t\targMax(reg.pgid,\n" +
				"\t\t\t\t\treg.receivetime) pgid,\n" +
				"\t\t\t\t\targMax(reg.gameid,\n" +
				"\t\t\t\t\treg.receivetime) AS gameid,\n" +
				"\t\t\t\t\targMax(reg.os,\n" +
				"\t\t\t\t\treg.receivetime) AS os,\n" +
				"\t\t\t\t\targMax(wpc.manage,\n" +
				"\t\t\t\t\treg.receivetime) investor,\n" +
				"\t\t\t\t\targMax(osu.dept_id,\n" +
				"\t\t\t\t\treg.receivetime) deptId,\n" +
				"\t\t\t\t\targMax(ad.adid,\n" +
				"\t\t\t\t\treg.receivetime) adidTmp,\n" +
				"\t\t\t\t\targMax(ad.adname,\n" +
				"\t\t\t\t\treg.receivetime) adidName,\n" +
				"\t\t\t\t\targMax(ad.adaccount,\n" +
				"\t\t\t\t\treg.receivetime) adAccount,\n" +
				"\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\twhen adidTmp is not null THEN adidTmp\n" +
				"\t\t\t\t\t\telse ''\n" +
				"\t\t\t\tend) as adid,\n" +
				"\t\t\t\t\trc.kid rckid,\n" +
				"\t\t\t\t\targMax(reg.kid,\n" +
				"\t\t\t\t\treg.receivetime) regkid,\n" +
				"\t\t\t\t\targMax(rc.uuid,\n" +
				"\t\t\t\t\treg.receivetime) AS payuuid,\n" +
				"\t\t\t\t\targMax(rc.fee,\n" +
				"\t\t\t\t\treg.receivetime) fee,\n" +
				"\t\t\t\t\targMax(rc.givemoney,\n" +
				"\t\t\t\t\treg.receivetime) givemoney\n" +
				"\t\t\t\tfrom\n" +
				"\t\t\t\t\toriginal_user_recharge rc\n" +
				"\t\t\t\tINNER JOIN odsmysql_wan_game g on\n" +
				"\t\t\t\t\trc.gameid = g.id\n" +
				"\t\t\t\tINNER JOIN thirty_game_device_reg reg on\n" +
				"\t\t\t\t\trc.uuid = reg.uuid\n" +
				"\t\t\t\t\tand g.pgid = reg.pgid\n" +
				"\t\t\t\tLEFT JOIN v_thirty_ad_device ad on\n" +
				"\t\t\t\t\treg.uuid = ad.uuid\n" +
				"\t\t\t\t\tand reg.pgid = ad.pgid\n" +
				"\t\t\t\t\tand reg.`day` = ad.`day`\n" +
				"\t\t\t\tLEFT JOIN odsmysql_wan_promotion_channel_v3 wpc ON\n" +
				"\t\t\t\t\twpc.isdelete = 0\n" +
				"\t\t\t\t\tAND wpc.parent_code = reg.parentchl\n" +
				"\t\t\t\t\tAND wpc.chncode = reg.chl\n" +
				"\t\t\t\tLEFT JOIN odsmysql_sys_user osu ON\n" +
				"\t\t\t\t\twpc.manage = osu.user_id\n" +
				"\t\t\t\tLEFT JOIN v_odsmysql_adid vad ON\n" +
				"\t\t\t\t\tad.adid = vad.adid\n" +
				"\t\t\t\t\tAND ad.ctype = vad.ctype\n" +
				"\t\t\t\twhere\n" +
				"\t\t\t\t\t1 = 1\n" +
				"\t\t\t\t\tand rc.`day` >= " + firstDayOfMonth +
				"\t\t\t\t\tand rc.`day` <= " + lastDayOfMonth +
				"\t\t\t\t\tAND rc.`day` >= reg.`day`\n" +
				"\t\t\t\tgroup by\n" +
				"\t\t\t\t\trc.kid ) a\n" +
				"\t\t\tWHERE\n" +
				"\t\t\t\t1 = 1\n" +
				whereCondition +
				"\t\t\tGROUP BY\n" +
				"\t\t\t\tadid,\n" +
				groupByCondition
				);
		querySql.append(" ) t003 ");
		return querySql;
	}

	//计算点消耗指标 t004
	private StringBuilder getAd(AdKpi req) {
		String selectCondition = getSelectCondition(req);
		String groupByCondition = getGroupByCondition(req);
		String whereCondition = getWhereCondition(req, "");
		Integer yearMonth = req.getYearMonth();
		String yearMonthStr = yearMonth.toString();
		String firstDayOfMonth = DateUtils.getFirstDayOfMonth(yearMonthStr);
		String lastDayOfMonth = DateUtils.getLastDayOfMonth(yearMonthStr);
		StringBuilder querySql = new StringBuilder();
		querySql.append(" ( ");
		querySql.append(" \n" +
				"\t\t\t-- 返点消耗\n" +
				"\t\t\tSELECT\n" +
				selectCondition +
				"\t\t\t\tadid,\n" +
				"\t\t\t\tSUM(cost) cost,\n" +
				"\t\t\t\tSUM(rudecost) rudecost\n" +
				"\t\t\tfrom\n" +
				"\t\t\t\t(\n" +
				"\t\t\t\tSELECT\n" +
				"\t\t\t\t\tb.date day,\n" +
				"\t\t\t\t\tparseDateTimeBestEffort(toString(day)) dayDate,\n" +
				"\t\t\t\t\ttoString(toYear(dayDate)) yearStr,\n" +
				"\t\t\t\t\ttoMonth(dayDate) monthInt,\n" +
				"\t\t\t\t\ttoYearWeek(dayDate,\n" +
				"\t\t\t\t\t3) weekInt,\n" +
				"\t\t\t\t\tconcat(yearStr,\n" +
				"\t\t\t\t\tIF(10>monthInt,\n" +
				"\t\t\t\t\t'0',\n" +
				"\t\t\t\t\t''),\n" +
				"\t\t\t\t\ttoString(monthInt)) AS monthStr,\n" +
				"\t\t\t\t\tconcat(toString(weekInt),\n" +
				"\t\t\t\t\t'周') AS weekStr,\n" +
				"\t\t\t\t\tmonthStr AS period,\n" +
				"\t\t\t\t\tIFNULL(g.pgid,\n" +
				"\t\t\t\t\t0) pgid,\n" +
				"\t\t\t\t\tIFNULL(ap.gameid,\n" +
				"\t\t\t\t\t0) gameid,\n" +
				"\t\t\t\t\tIFNULL(g.os, 3) os,\n" +
				"\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\twhen ap.adid is not null\n" +
				"\t\t\t\t\t\tand ap.adid != '' then ap.parentchl\n" +
				"\t\t\t\t\t\telse '-'\n" +
				"\t\t\t\tend) parentchl,\n" +
				"\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\twhen ap.adid is not null\n" +
				"\t\t\t\t\t\tand ap.adid != '' then ap.chl\n" +
				"\t\t\t\t\t\telse '-'\n" +
				"\t\t\t\tend) chl,\n" +
				"\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\twhen ap.adid is not null\n" +
				"\t\t\t\t\t\tand ap.adid != '' then ap.appchl\n" +
				"\t\t\t\t\t\telse '-'\n" +
				"\t\t\t\tend) appchl,\n" +
				"\t\t\t\t\tosu.user_id investor,\n" +
				"\t\t\t\t\tosu.dept_id deptId,\n" +
				"\t\t\t\t\tb.ad_id adid,\n" +
				"\t\t\t\t\tb.ad_name adidName,\n" +
				"\t\t\t\t\tb.ad_account adAccount,\n" +
				"\t\t\t\t\tb.adconvert adconvert,\n" +
				"\t\t\t\t\tb.adshow adshow,\n" +
				"\t\t\t\t\tb.click click,\n" +
				"\t\t\t\t\tb.rudecost rudecost,\n" +
				"\t\t\t\t\tb.cost cost\n" +
				"\t\t\t\tfrom\n" +
				"\t\t\t\t\tv_adid_rebate_day b\n" +
				"\t\t\t\tleft JOIN ad_ptype ap on\n" +
				"\t\t\t\t\tap.adid = b.ad_id\n" +
				"\t\t\t\tleft JOIN v_odsmysql_wan_game g on\n" +
				"\t\t\t\t\tap.gameid = g.id\n" +
				"\t\t\t\tLEFT JOIN odsmysql_wan_promotion_channel_v3 wpc ON\n" +
				"\t\t\t\t\twpc.isdelete = 0\n" +
				"\t\t\t\t\tAND wpc.parent_code = ap.parentchl\n" +
				"\t\t\t\t\tAND wpc.chncode = ap.chl\n" +
				"\t\t\t\tLEFT JOIN odsmysql_ad_account aa ON\n" +
				"\t\t\t\t\taa.advertiser_id = b.ad_account\n" +
				"\t\t\t\tLEFT JOIN odsmysql_sys_user osu ON\n" +
				"\t\t\t\t\taa.throw_user = toString(osu.user_id)\n" +
				"\t\t\t\tLEFT JOIN v_odsmysql_adid vad ON\n" +
				"\t\t\t\t\tb.ad_id = vad.adid\n" +
				"\t\t\t\t\tAND b.ctype = vad.ctype\n" +
				"\t\t\t\tWHERE\n" +
				"\t\t\t\t\t1 = 1\n" +
				"\t\t\t\t\tand b.date >= " + firstDayOfMonth +
				"\t\t\t\t\tand b.date <= " + lastDayOfMonth +
				" ) a \n" +
				"\t\t\twhere\n" +
				"\t\t\t\t1 = 1\n" +
				whereCondition +
				"\t\t\tGROUP BY\n" +
				"\t\t\t\tadid,\n" +
				groupByCondition
				);
		querySql.append(" ) t004 ");
		return querySql;
	}

}
